<?php
// ********************************************
// 
// Каталог автомобилей
//
// ********************************************

require_once( "tools.class.php" );
require_once( "tables.class.php" );    
require_once( "datadefine.class.php" );

class Cars extends tables 
{
    public function GetPage()
    {

        $data = new jqGridData();

        while( true )
        {
            if( $this->db == NULL ) {
                $data->userdata->errcode = 1;
                $data->userdata->errmess = "Ошибка соединения с базой данных.";
                break;
            }
            $db = $this->db;
            
// Проверяем данные
            $page    = ( isset($_REQUEST["page"]) && $_REQUEST["page"] > 0 ) ? $_REQUEST["page"] : 1;
            $limit   = ( isset($_REQUEST["rows"]) && $_REQUEST["rows"] > 0 ) ? $_REQUEST["rows"] : 1;
            $sidx    = ( isset($_REQUEST["sidx"]) && $_REQUEST["sidx"] != '' ) ? $_REQUEST["sidx"] : 'car_id';
            $sord    = ( isset($_REQUEST["sord"]) && strtolower($_REQUEST["sord"]) == 'desc' ) ? 'DESC' : 'ASC';
            $search  = ( isset($_REQUEST["_search"]) && strtolower($_REQUEST["_search"]) == 'true') ? true : false;

            switch ($sidx)
            {
                case "vend_id":     $sidx = "cars.vend_id"; break;
                case "vend_name":   $sidx = "vdic.vend_name"; break;
                case "model_id":    $sidx = "cars.model_id"; break;
                case "model_name":  $sidx = "mdic.model_name"; break;
                case "color_id":    $sidx = "cars.color_id"; break;
                case "color_name":  $sidx = "cdic.color_name"; break;
                case "engine":      $sidx = "cars.engine"; break;
                case "price":       $sidx = "price.price"; break;
                case "comment":     $sidx = "cars.comment"; break;
                default: $sidx = "cars.car_id"; break;
            }
                    
// Фильтр
            $whc = array();
            if( $search )
            {
                if (isset($_REQUEST["car_id"]) && is_numeric($_REQUEST["car_id"]) ) {
                    $whc[] = "car_id=" . $db->quote($_REQUEST["car_id"]);
                }
                if(isset($_REQUEST["vend_id"]) && is_numeric($_REQUEST["vend_id"]) ) {
                    $whc[] = "vend_id=" . $db->quote($_REQUEST["vend_id"]);
                }
                if(isset($_REQUEST["vend_name"]) && $_REQUEST["vend_name"] != "") {
                    $whc[] = "vend_name LIKE " . $db->quote("%".$_REQUEST["vend_name"]."%");
                }
                if(isset($_REQUEST["model_id"]) && is_numeric($_REQUEST["model_id"]) ) {
                    $whc[] = "model_id=" . $db->quote($_REQUEST["model_id"]);
                }
                if(isset($_REQUEST["model_name"]) && $_REQUEST["model_name"] != "") {
                    $whc[] = "model_name LIKE " . $db->quote("%".$_REQUEST["model_name"]."%");
                }
                if(isset($_REQUEST["color_id"]) && is_numeric($_REQUEST["color_id"]) ) {
                    $whc[] = "color_id=" . $db->quote($_REQUEST["color_id"]);
                }
                if(isset($_REQUEST["color_name"]) && $_REQUEST["color_name"] != "") {
                    $whc[] = "color_name LIKE " . $db->quote("%".$_REQUEST["color_name"]."%");
                }
                if(isset($_REQUEST["engine"]) && $_REQUEST["engine"] != "") {
                    $engine = str_replace(',', '.', $_REQUEST["engine"]);
                    $whc[] = "LTRIM(cars.engine) LIKE " . $db->quote($_REQUEST["engine"]."%");
                }
                if( isset($_REQUEST["comment"]) && $_REQUEST["comment"] != "") {
                    $whc[] = "comment LIKE " . $db->quote("%".$_REQUEST["comment"]."%");
                }
            }
            $whereclaus = (count($whc) != 0 ) ? " WHERE " . implode(" AND ", $whc) : "";
            unset($whc);

// Получаем количество записей с учетом фильтра
            $joins = 
                "INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id ".
                "INNER JOIN models AS mdic ON mdic.model_id = cars.model_id ".
                "INNER JOIN colors AS cdic ON cdic.color_id = cars.color_id ".
                "LEFT JOIN price ON price.car_id = cars.car_id AND price.active='1' ";
            
            $sqlreq = "SELECT COUNT(*) AS count FROM cars ".$joins.$whereclaus;
                
            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();

// Пустая таблица
            if ($count == 0) break;

// Рассчитаем сколько всего страниц займут данные в БД
            if( $count > 0 && $limit > 0) {
                $total_pages = ceil($count/$limit);
            } else {
                $total_pages = 0;
            }                  
 
            if ($page > $total_pages) $page=$total_pages;                  
 
            $start = $limit*$page - $limit;                  
            if($start < 0) $start = 0;                  
 
// Формируем запрос
            $sqlreq =   "SELECT cars.car_id AS car_id, cars.vend_id AS vend_id, cars.model_id AS model_id, ".
                        "cars.color_id AS color_id, cars.engine AS engine, cars.comment AS comment, ".
                        "vdic.vend_name AS vend_name, mdic.model_name AS model_name, cdic.color_name AS color_name, ".
                        "price.price AS price FROM cars ".$joins.$whereclaus.
                        " ORDER BY ".$sidx." ".$sord." LIMIT ".$start.", ".$limit;
            
            $recset = $db->query($sqlreq);                  
            
// Формирование объекта для возврата
            $data->page      = $page;
            $data->total     = $total_pages;
            $data->records   = $count;
        
// Строки данных для таблицы
            $i = 0;
            while($row = $recset->fetch(PDO::FETCH_ASSOC)) {
                $data->rows[$i]['id'] = $row['car_id'];
                $data->rows[$i]['cell'] = array(
                        $row['car_id'],
                        $row['vend_id'],
                        $row['vend_name'],
                        $row['model_id'],
                        $row['model_name'],
                        $row['color_id'],
                        $row['color_name'],
                        $row['engine'],
                        is_null($row['price']) ? "н/д" : $row['price'],
                        $row['comment']
                );
                $i++;
            }
            break;
        }
        
// Отдаем результат
        header("Content-type: text/script;charset=utf-8");
        echo json_encode($data);
        return;
    }

    
    public function Insert()
    {
        $car_id   = isset($_REQUEST["id"]) ? $_REQUEST["id"] : '';
        $vend_id  = isset($_REQUEST["vend_id"]) ? $_REQUEST["vend_id"] : '';
        $model_id = isset($_REQUEST["model_id"]) ? $_REQUEST["model_id"] : '';
        $color_id = isset($_REQUEST["color_id"]) ? $_REQUEST["color_id"] : '';
        $engine   = isset($_REQUEST["engine"]) ? $_REQUEST["engine"] : '';
        $engine   = str_replace(',', '.', $_REQUEST["engine"]);
        $comment  = isset($_REQUEST["comment"]) ? $_REQUEST["comment"] : '';

        $errmess = "";
        
        while (true)
        {
            if ($car_id != "_empty" || !is_numeric($vend_id) || 
                !is_numeric($model_id) || !is_numeric($color_id) ||
                 !is_numeric($engine) )
            {
                $errmess = "Нарушение формата данных";
                break;
            }

             if( $this->db == NULL ) {
                $errmess = "Ошибка соединения с базой данных.";
                break;
            }
            $db = $this->db;
            
// Проверяем существование
            $sqlreq = "SELECT COUNT(*) AS count FROM cars WHERE (".
                " vend_id=".$db->quote($vend_id)." AND model_id=".$db->quote($model_id).
                " AND color_id=".$db->quote($color_id)." AND engine=".$db->quote($engine)." )";

            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            if ($count > 0) {
                $errmess = "В справочнике уже присутствует автомобиль с указанными атрибутами.";
                break;
            }
            
            $sqlreq = "INSERT INTO cars (vend_id, model_id, color_id, engine, comment) VALUES (".
                $db->quote($vend_id).", ".$db->quote($model_id).", ".$db->quote($color_id).", ".
                $db->quote($engine).", ".$db->quote($comment).")";

            if( $db->exec($sqlreq) === false ){
                $errmess = "Ошибка добавления новой записи.";
                break;
            }

            break;
        }

        Tools::json_response( $errmess, $errmess ? 1 : 0 );            
        return;
    }

    
    public function Update()
    {
        $car_id   = isset($_REQUEST["id"]) ? $_REQUEST["id"] : '';
        $vend_id  = isset($_REQUEST["vend_id"]) ? $_REQUEST["vend_id"] : '';
        $model_id = isset($_REQUEST["model_id"]) ? $_REQUEST["model_id"] : '';
        $color_id = isset($_REQUEST["color_id"]) ? $_REQUEST["color_id"] : '';
        $engine   = isset($_REQUEST["engine"]) ? $_REQUEST["engine"] : '';
        $engine   = str_replace(',', '.', $_REQUEST["engine"]);
        $comment  = isset($_REQUEST["comment"]) ? $_REQUEST["comment"] : '';

        $errmess = "";
        
        while (true)
        {
            if( !is_numeric($car_id) || !is_numeric($vend_id) || 
                !is_numeric($model_id) || !is_numeric($color_id) ||
                !is_numeric($engine) )
            {
                $errmess = "Нарушение формата данных";
                break;
            }

             if( $this->db == NULL ) {
                $errmess = "Ошибка соединения с базой данных.";
                break;
            }
            $db = $this->db;
            
            // Проверяем сеществование
            $sqlreq = "SELECT COUNT(*) AS count FROM cars WHERE (".
                " vend_id=".$db->quote($vend_id)." AND model_id=".$db->quote($model_id).
                " AND color_id=".$db->quote($color_id)." AND engine=".$db->quote($engine).
                " AND car_id != ".$db->quote($car_id)." )";

            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            if ($count > 0) {
                $errmess = "В справочнике уже присутствует автомобиль с указанными атрибутами.";
                break;
            }
            
            $sqlreq = "UPDATE cars SET ".
                "vend_id=".$db->quote($vend_id).", ".
                "model_id=".$db->quote($model_id).", ".
                "color_id=".$db->quote($color_id).", ".
                "engine=".$db->quote($engine).", ".
                "comment=".$db->quote($comment)." ".
                "WHERE car_id=".$db->quote($car_id);

            if( $db->exec($sqlreq) === false ){
                $errmess = "Ошибка обновления записи.";
                break;
            }

            break;
        }

        Tools::json_response( $errmess, $errmess ? 1 : 0 );            
        return;
    }


    public function Delete()
    {
        $errmess = "";
        $car_ids = (isset($_REQUEST['id']) && $_REQUEST['id'] != '') ?
            explode(",", $_REQUEST["id"], 100) : array();

        while( true ) {
            if (!Tools::is_numeric_array( $car_ids )) {
                $errmess = "Ошибка удаления записи.";
                break;
            }

            $car_id_list = implode(',', $car_ids);
            
            if( $this->db == NULL ) {
                $errmess = "Ошибка соединения с базой данных.";
                break;
            }
            $db = $this->db;
        
            $sqlreq =
               "START TRANSACTION;".
               "DELETE FROM cars WHERE car_id IN (".$car_id_list.");".
               "DELETE FROM price WHERE car_id IN (".$car_id_list.");";

            if( $db->exec($sqlreq) === false ){
                $db->exec("ROLLBACK;");
                $errmess = "Ошибка удаления записи(сей).";
                break;
            }
            else {
                $this->db->exec("COMMIT;");
            }

            break;
        }

        Tools::json_response( $errmess, $errmess ? 1 : 0 );            
        return;
    }


    // 
    // Каталог автомобилей
    //
    public function Find()
    {
        $data = new jqGridData();

        while( true )
        {
            if( $this->db == NULL ) {
                $data->userdata->errcode = 1;
                $data->userdata->errmess = "Ошибка соединения с базой данных.";
                break;
            }
            $db = $this->db;
            
// Проверяем данные
            $page    = ( isset($_REQUEST["page"]) && $_REQUEST["page"] > 0 ) ? $_REQUEST["page"] : 1;
            $limit   = ( isset($_REQUEST["rows"]) && $_REQUEST["rows"] > 0 ) ? $_REQUEST["rows"] : 1;
            $sidx    = ( isset($_REQUEST["sidx"]) && $_REQUEST["sidx"] != '' ) ? $_REQUEST["sidx"] : 'car_id';
            $sord    = ( isset($_REQUEST["sord"]) && strtolower($_REQUEST["sord"]) == 'desc' ) ? 'DESC' : 'ASC';
            $search  = ( isset($_REQUEST["_search"]) && strtolower($_REQUEST["_search"]) == 'true') ? true : false;

            switch ($sidx)
            {
                case "vend_id":     $sidx = "cars.vend_id"; break;
                case "vend_name":   $sidx = "vdic.vend_name"; break;
                case "model_id":    $sidx = "cars.model_id"; break;
                case "model_name":  $sidx = "mdic.model_name"; break;
                case "color_id":    $sidx = "cars.color_id"; break;
                case "color_name":  $sidx = "cdic.color_name"; break;
                case "engine":      $sidx = "cars.engine"; break;
                case "price":       $sidx = "prc.price"; break;
                case "comment":     $sidx = "cars.comment"; break;
                default: $sidx = "cars.car_id"; break;
            }
                
// Фильтр
            $whc = array();
            if( $search )
            {
                if (isset($_REQUEST["car_id"]) && is_numeric($_REQUEST["car_id"]) ) {
                    $whc[] = "car_id=" . $db->quote($_REQUEST["car_id"]);
                }
                if(isset($_REQUEST["vend_id"]) && is_numeric($_REQUEST["vend_id"]) ) {
                    $whc[] = "vend_id=" . $db->quote($_REQUEST["vend_id"]);
                }
                if(isset($_REQUEST["model_id"]) && is_numeric($_REQUEST["model_id"]) ) {
                    $whc[] = "model_id=" . $db->quote($_REQUEST["model_id"]);
                }
                if(isset($_REQUEST["color_id"]) && is_numeric($_REQUEST["color_id"]) ) {
                    $whc[] = "color_id=" . $db->quote($_REQUEST["color_id"]);
                }
                if(isset($_REQUEST["vend_name"]) && $_REQUEST["vend_name"] != "") {
                    $whc[] = "vend_name LIKE " . $db->quote("%".$_REQUEST["vend_name"]."%");
                }
                if(isset($_REQUEST["model_name"]) && $_REQUEST["model_name"] != "") {
                    $whc[] = "model_name LIKE " . $db->quote("%".$_REQUEST["model_name"]."%");
                }
                if(isset($_REQUEST["color_name"]) && $_REQUEST["color_name"] != "") {
                    $whc[] = "color_name LIKE " . $db->quote("%".$_REQUEST["color_name"]."%");
                }
                if(isset($_REQUEST["engine"]) && $_REQUEST["engine"] != ""){
                    $whc[] = "LTRIM(cars.engine) LIKE " . $db->quote(str_replace(',', '.', $_REQUEST["engine"])."%");
                }
                if (isset($_REQUEST["lprice"]) && $_REQUEST["lprice"] != "") {
                    $whc[] = "prc.price >= ".$db->quote(str_replace(',', '.', $_REQUEST["lprice"]));
                }
                if (isset($_REQUEST["hprice"]) && $_REQUEST["hprice"] != "") {
                    $whc[] = "prc.price <= ".$db->quote(str_replace(',', '.', $_REQUEST["hprice"]));
                }
            }
            $whereclaus = (count($whc) != 0 ) ? " WHERE " . implode(" AND ", $whc) : "";
            unset($whc);
                
            // Получаем количество записей с учетом фильтра
            $price_joining = "";
            if(isset($_REQUEST["s_date"]) && preg_match("/(\d\d)\.(\d\d)\.(\d\d\d\d)/", $_REQUEST["s_date"], $d) && 
                checkdate($d[2],$d[1],$d[3]) )
            {            
                $price_joining = 
                    "( SELECT car_id, setdate, price FROM price AS s1".
                    " WHERE (setdate = (SELECT MAX(setdate) AS max_s_date FROM price AS s2".
                    " WHERE (s2.car_id = s1.car_id) AND (s2.setdate <= ".$db->quote($d[3].'-'.$d[2].'-'.$d[1])."))) )";
            }
            else
            {
                $price_joining = "( SELECT car_id, setdate, price FROM price WHERE active='1' )";
            }
            
            $joins = 
                "INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id ".
                "INNER JOIN models AS mdic ON mdic.model_id = cars.model_id ".
                "INNER JOIN colors AS cdic ON cdic.color_id = cars.color_id ".
                "LEFT  JOIN ".$price_joining." AS prc ON prc.car_id = cars.car_id";
                
            $sqlreq = "SELECT COUNT(*) AS count FROM cars ".$joins.$whereclaus;
            
            $recset = $db->query( $sqlreq );
            $row = $recset->fetch(PDO::FETCH_ASSOC);
            $count = $row['count'];
            $recset->closeCursor();
            
// Пустая таблица
            if ($count == 0) break;
            
// Рассчитаем сколько всего страниц займут данные в БД
            if( $count > 0 && $limit > 0) {
                $total_pages = ceil($count/$limit);
            } else {
                $total_pages = 0;
            }                  
            
            if ($page > $total_pages) $page=$total_pages;                  

            $start = $limit*$page - $limit;                  
            if($start < 0) $start = 0;                  
            
 // Формируем запрос
            $sqlreq = 
                  "SELECT cars.car_id AS car_id, cars.vend_id AS vend_id, vdic.vend_name AS vend_name, ".
                  "cars.model_id AS model_id, mdic.model_name AS model_name, cars.color_id AS color_id, ".
                  "cdic.color_name AS color_name, cars.engine AS engine, prc.price AS price, ".
                  "cars.comment AS comment FROM cars ".$joins.$whereclaus.
                  " ORDER BY ".$sidx." ".$sord." LIMIT ".$start.", ".$limit;
                  
            $recset = $db->query($sqlreq);                  
            
// Форми    рование объекта для возврата
            $data->page      = $page;
            $data->total     = $total_pages;
            $data->records   = $count;
            
// Строк    и данных для таблицы
            $i = 0;
            while($row = $recset->fetch(PDO::FETCH_ASSOC)) {
                $data->rows[$i]['id'] = $row['car_id'];
                $data->rows[$i]['cell'] = array(
                        $row['car_id'],
                        $row['vend_id'],
                        $row['vend_name'],
                        $row['model_id'],
                        $row['model_name'],
                        $row['color_id'],
                        $row['color_name'],
                        $row['engine'],
                        is_null($row['price']) ? "н/д" : $row['price'],
                        $row['comment']
                );
                $i++;
            }
            break;
        }
        
// Отдаем результат
        header("Content-type: text/script;charset=utf-8");
        echo json_encode($data);
        return;
    }

    public function GetList() 
    {
        return;
    }
    
} // End of Cars Catalogue 

?>
